﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using Could.Service;

namespace CatSentenceAnalyzer
{
    public interface ISQLEngine
    {
        MySqlDataReader SelectFrom();
        Boolean UpdateFromID();
        Boolean DeleteFromID();
        int InsertInto();
        NPClass m_npc
        {
            get;
            set;
        }
    }
    public abstract class SqlDB : ISQLEngine
    {
        static public string INDEXFIELD = "ANALYZED";
        static public string TEXTFIELD = "TEXTFIELD";
        static public string WLENFIELD = "WORDLEN";
        static public string SUMFIELD = "SUMKEY";
        static public string IDFIELD = "ID";
        static public string USAGECOUT = "UsageCount";

        public string m_ConnectionString;
        public string m_sQuery;
        public abstract MySqlDataReader SelectFrom();
        public abstract Boolean UpdateFromID();
        public abstract Boolean DeleteFromID();
        public abstract int InsertInto();

        private NPClass _m_npc;
        public NPClass m_npc
        {
            get
            {
                return _m_npc;
            }
            set
            {
                m_ConnectionString = value.DbSPrivate.sqlconnection;
                _m_npc = value;
            }
        }

        private DbServerData _pDbsd;
        public virtual DbServerData pDbSd
        {
            get { return _pDbsd;  }
        }

        private MySqlConnection m_cnn= null;
        public MySqlConnection Connection
        {
            get{
                if (m_cnn==null)
                    m_cnn = new MySqlConnection(m_ConnectionString);
                if (m_cnn.State!=System.Data.ConnectionState.Open)
                {
                    m_cnn.Open();
                }
                return m_cnn;
            }
        }

        private Dictionary<int, string> m_dict = null;
        private Dictionary<int, string> SQLRef()
        {
            m_dict = new Dictionary<int, string>();
            m_dict.Add(39, "\\'");
            m_dict.Add(34, "\\\"");
            m_dict.Add(0, "\\\0");
            m_dict.Add(8, "\\\b");
            m_dict.Add(10, "\\\n");
            m_dict.Add(13, "\\\r");
            m_dict.Add(9, "\\\t");
            m_dict.Add(26, "\\z");
            m_dict.Add(92, "\\");
            m_dict.Add(37, "\\%");
            m_dict.Add(95, "\\_");
            return m_dict;
        }

        public string SQLEncode(string str)
        {
            string s = "";
            if (m_dict == null) m_dict = SQLRef();
            char[] chs = str.ToCharArray();
            for (int i = 0; i < chs.Length; i++)
            {
                if (m_dict.ContainsKey(chs[i]))
                {
                    s += m_dict[chs[i]];
                }
                else
                {
                    s += chs[i];
                }
            }
            return s;
        }
    }

    public class MysqlDB : SqlDB
    {
        private MySqlCommand Command
        {
            get{
                return new MySqlCommand( m_sQuery, Connection);
            }
        }
        private MySqlCommand CommandSQL( string sql)
        {
             return new MySqlCommand(sql, Connection);
        }
        private Boolean ExecuteNonQuery()
        {
            return Command.ExecuteNonQuery() > 0;
        }
        public override MySqlDataReader SelectFrom()
        {
            return Command.ExecuteReader();
        }

        public override Boolean DeleteFromID()
        {
            return ExecuteNonQuery();
        }
        public override Boolean UpdateFromID()
        {
            return ExecuteNonQuery();
        }
        public override int InsertInto()
        {
            return Insert();
        }
        public int Insert()
        {
            string sql = m_sQuery + ";SELECT @@identity;";
            return Convert.ToInt32(CommandSQL(sql).ExecuteScalar());
        }
    }
    public class TmsMysql : MysqlDB
    {
        //<tms name="sphinx_cat.ta_private_|T|" sql="SELECT * FROM sphinx_cat.ta_private_|T| WHERE Userid=|U| AND FileID=|F|" form="1" t="50" />
        public override MySqlDataReader SelectFrom()
        {
            string sql = pDbSd.tmsquery;
            sql = sql.Replace("|T|", pDbSd.itable );
            sql = sql.Replace("|U|", m_npc.UserID.ToString());
            sql = sql.Replace("|F|", m_npc.FileID.ToString());
            m_sQuery = sql;
            return base.SelectFrom();
        }

        public override bool DeleteFromID()
        {
            m_sQuery = @"DELETE FROM " + pDbSd.tmstable + " WHERE ID=" + m_npc.transdata.TmsID.ToString();
            return base.DeleteFromID();
        }

        public bool UpdateFrom_UsageCount(string sid)
        {
            m_sQuery = @"UPDATE " + pDbSd.tmstable + " SET UsageCount=UsageCount+1, LastUsageDate=Now()  WHERE ID=" + sid;
            return base.UpdateFromID();
        }

        public override bool UpdateFromID()
        {
            m_sQuery = @"UPDATE " + pDbSd.tmstable + " SET " + m_npc.transdata.textTmsField + "='" + SQLEncode(m_npc.transdata.TSentence) + "', "
            + m_npc.transdata.indexTmsField + "='" + SQLEncode(m_npc.transdata.OSentence) + "', TLastTime= NOW()  WHERE ID=" + m_npc.transdata.TmsID.ToString();

            return base.UpdateFromID();
        }
        public override int InsertInto()
        {
            m_sQuery = @"INSERT INTO " + pDbSd.tmstable + " (FileID,TLastTime,LastUsageDate,userid," + m_npc.transdata.indexTmsField + "," +  m_npc.transdata.textTmsField + ") VALUES(" +
                m_npc.FileID.ToString() + ",NOW(),NOW()," + m_npc.UserID.ToString() + ",'" + SQLEncode(m_npc.transdata.OSentence) + "','" + SQLEncode(m_npc.transdata.TSentence) + "')";
            return base.InsertInto();
        }
    }
    public class TdbMysql : MysqlDB
    {
        public int wordlen = 0, sumkey = 0;
        //<tdb name="sphinx_cat.tdb_library_|T|" sql="SELECT * FROM sphinx_cat.tdb_library_|T| WHERE Userid=|U| AND FileID=|F|" form="1" t="50" />
        public override MySqlDataReader SelectFrom()
        {
            string sql = pDbSd.tdbquery;
            sql = sql.Replace("|T|", pDbSd.itable);
            sql = sql.Replace("|U|", m_npc.UserID.ToString());
            sql = sql.Replace("|F|", m_npc.FileID.ToString());
            m_sQuery = sql;
            return base.SelectFrom();
        }

        public override bool DeleteFromID()
        {
            m_sQuery = @"DELETE FROM " + pDbSd.tdbtable + " WHERE ID=" + m_npc.transdata.TdbID.ToString();
            return base.DeleteFromID();
        }

        public override bool UpdateFromID()
        {
            m_sQuery = @"UPDATE " + pDbSd.tdbtable + " SET " + m_npc.transdata.textTdbField + "='" + SQLEncode( m_npc.transdata.TLibrary) + "', "
                + m_npc.transdata.indexTdbField + "='" + SQLEncode(m_npc.transdata.OLibrary) + "', TLastTime= NOW()  WHERE ID=" + m_npc.transdata.TdbID.ToString();
            return base.UpdateFromID();
        }

        public override int InsertInto()
        {
            m_sQuery = @"INSERT INTO " + pDbSd.tdbtable + " (FileID,TLastTime,userid," + m_npc.transdata.indexTdbField + "," + m_npc.transdata.textTdbField + ",wordlen, sumkey) VALUES(" +
                m_npc.FileID.ToString() + ",NOW()," + m_npc.UserID.ToString() + ",'" + SQLEncode(m_npc.transdata.OLibrary) + "','" + SQLEncode(m_npc.transdata.TLibrary) + "'," +
                wordlen.ToString() + "," + sumkey.ToString() + ")"; 
            return base.InsertInto();
        }
    }

    public class PrivateTdbMysql : TdbMysql
    {
        public override DbServerData pDbSd
        {
            get
            {
                return m_npc.DbSPrivate;
            }
        }
    }
    public class PrivateTmsMysql : TmsMysql
    {
        public override DbServerData pDbSd
        {
            get
            {
                return m_npc.DbSPrivate;
            }
        }
    }

    public class MainTmsMysql : TmsMysql
    {
        public override DbServerData pDbSd
        {
            get
            {
                return m_npc.DbSMain;
            }
        }
        public override int InsertInto()
        {
            m_sQuery = @"INSERT INTO " + pDbSd.tmstable + " (TLastTime,LastUsageDate,userid," + m_npc.transdata.indexTmsField + "," + m_npc.transdata.textTmsField + ") VALUES(" +
                "NOW(),NOW()," + m_npc.UserID.ToString() + ",'" + SQLEncode(m_npc.transdata.OSentence) + "','" + SQLEncode(m_npc.transdata.TSentence) + "')";
            return Insert();
        }
    }
    public class MainTdbMysql : TdbMysql
    {
        public override DbServerData pDbSd
        {
            get
            {
                return m_npc.DbSMain;
            }
        }
        public override int InsertInto()
        {
            m_sQuery = @"INSERT INTO " + pDbSd.tdbtable + " (TLastTime,userid," + m_npc.transdata.indexTdbField + "," + m_npc.transdata.textTdbField + ",wordlen, sumkey) VALUES(" +
                "NOW()," + m_npc.UserID.ToString() + ",'" + SQLEncode(m_npc.transdata.OLibrary) + "','" + SQLEncode(m_npc.transdata.TLibrary) + "'," +
                wordlen.ToString() + "," + sumkey.ToString() + ")";
            return Insert();
        }

    }
}



